SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[usp_GetLectureByNo] ( @JOL_Lecture_No INT )
AS 
    DECLARE @ID INT
    SET @ID = (
                SELECT  JOL_Lecture_Purpose
                FROM    dbo.JOL_Lecture
                WHERE   JOL_Lecture_No = @JOL_Lecture_No
              )
    SELECT  c.JOL_Employer_Name,
            a.JOL_Lecture_Title,
            a.JOL_Lecture_Linkman,
            a.JOL_Lecture_Mobile,
            a.JOL_Lecture_Email,
            a.JOL_Lecture_Phone,
            a.JOL_Lecture_Fax,
        
        --
            a.JOL_Lecture_IsSetTop,
            a.JOL_Lecture_RequireNumber,
            a.JOL_Lecture_RequireStudyLevel,
            a.JOL_Lecture_Body,
            b.JOL_Field_Remark,
            a.JOL_Lecture_AddTime,
            a.JOL_Lecture_AddUser,
            a.JOL_Lecture_UpdateTime,
            a.JOL_Lecture_UpdateUser,
            a.JOL_Lecture_VerifyStatus,
            a.JOL_Lecture_VerifyUser,
            a.JOL_Lecture_IsSendEmail,
            b.JOL_Field_ApplyTime,
            b.JOL_Field_ApplyPosition,
            b.JOL_Field_SeatNumber,
            b.JOL_Field_IsProvideMultimedia,
            b.JOL_Field_IsNeedInvoice,
            b.JOL_Field_InvoiceBeginning,
            b.JOL_Field_InvoiceProject,
            b.JOL_Field_IsPaidMoney,
            a.JOL_Lecture_ClickNumber,
            a.JOL_Lecture_VerifyTime,
            b.JOL_ApplyField_EndTime
    FROM    dbo.JOL_Lecture AS a
            INNER JOIN dbo.JOL_ApplyField AS b ON a.JOL_Lecture_Purpose=b.JOL_Field_No
            INNER JOIN dbo.JOL_Employer AS c ON b.JOL_Field_ApplyCompany = c.JOL_Employer_Account
    WHERE   b.JOL_Field_No = @ID
            AND a.JOL_Lecture_No = @JOL_Lecture_No


GO
